package net.gz01.wallet.web.service;


import java.io.File;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Objects;

import org.jxls.common.Context;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import lombok.extern.slf4j.Slf4j;
import net.gz01.util.ZODate;
import net.gz01.util.ZOExcel;
import net.gz01.wallet.web.config.SystemEnum;
import net.gz01.wallet.web.config.SystemEnum.BizType;
import net.gz01.wallet.web.config.SystemEnum.ChannelCode;
import net.gz01.wallet.web.config.SystemEnum.FilePath;
import net.gz01.wallet.web.config.SystemEnum.PayOrderItemStatus;
import net.gz01.wallet.web.model.Account;
import net.gz01.wallet.web.model.ClearingIncome;
import net.gz01.wallet.web.model.PayOrderItem;
import net.gz01.wallet.web.model.TradeConfig;
import net.gz01.wallet.web.model.TradeOrder;

@Slf4j
public class ReportService {
	private static final String REPORT_PATH = SystemEnum.FilePath.ReportTmp.getValue();
	private static final String TEMPLATE = SystemEnum.FilePath.ReportTemplate.getValue();

	/**
	 * 虚账余额报表
	 *
	 * @return
	 */
	public File virtualBalance() {
		List<Record> dataList = Db.find("SELECT accountNo, SUM(balance) AS balance FROM account");
		Account channelIncome = Account.dao.findFirst("SELECT * FROM account WHERE accountNo='C00001'");
		List<Account> dataList2 = Account.dao.find("SELECT * FROM account WHERE balance > 0 ORDER BY channelNo,balance DESC");
		if (dataList.isEmpty() || dataList2.isEmpty()) {
			log.info("无数据");
			return null;
		}
		List<Record> balanceSumList = new ArrayList<>();
		for (Record r : dataList) {
			if(Objects.equals(r.get("accountNo"), channelIncome.getAccountNo())) {
				r.set("income", channelIncome.getBalance());
			}
			balanceSumList.add(r);
		}

		Context context1 = new Context();
		context1.putVar("list", balanceSumList);

		Context context2 = new Context();
		context2.putVar("list2", dataList2);

		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "virtual_balance.xlsx";
		String targetPath = REPORT_PATH + "虚账余额" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("虚账余额渠道汇总", context1);
		sheetNameAndData.put("虚账余额明细", context2);
		String[] templateSheetName = {"sheet1", "sheet2"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出虚账余额报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}


	public File virtualBalance_youli() {
		List<Record> dataList = Db.find("SELECT SUM(balance) AS balance FROM account");
//		Account channelIncome = Account.dao.findFirst("SELECT * FROM account WHERE accountNo='C00001'");
		List<Account> dataList2 = Account.dao.find("SELECT * FROM account  WHERE  accountNo <> ?" +
				" ORDER BY balance DESC", Account.default_merchCode);
		if (dataList.isEmpty() || dataList2.isEmpty()) {
			log.info("无数据");
			return null;
		}
		List<Record> balanceSumList = new ArrayList<>();
		for (Record r : dataList) {
//			if(Objects.equals(r.get("accountNo"), channelIncome.getAccountNo())) {
//				r.set("income", channelIncome.getBalance());
//			}
			balanceSumList.add(r);
		}

		Context context1 = new Context();
		context1.putVar("list", balanceSumList);

		Context context2 = new Context();
		context2.putVar("list2", dataList2);

		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "virtual_balance.xlsx";
		String targetPath = REPORT_PATH + "虚账余额" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("虚账余额渠道汇总", context1);
		sheetNameAndData.put("虚账余额明细", context2);
		String[] templateSheetName = {"sheet1", "sheet2"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出虚账余额报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}


	public File income(String startSettleDate, String endSettleDate) {
		List<ClearingIncome> list = ClearingIncome.dao.find("SELECT * FROM clearing_income WHERE type='0' AND settleDate BETWEEN ? AND ?", startSettleDate, endSettleDate);
		if (list.isEmpty()) {
			return null;
		}
		Context context = new Context();
		context.putVar("list", list);
		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "income.xlsx";
		String targetPath = REPORT_PATH + "收入" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("渠道收入", context);
		String[] templateSheetName = {"sheet1"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出渠道收入报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}

	public File share(String startSettleDate, String endSettleDate) {
		List<ClearingIncome> list = ClearingIncome.dao.find("SELECT * FROM clearing_income WHERE type='1' AND settleDate BETWEEN ? AND ?", startSettleDate, endSettleDate);
		if (list.isEmpty()) {
			return null;
		}
		Context context = new Context();
		context.putVar("list", list);
		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "share.xlsx";
		String targetPath = REPORT_PATH + "机构分润" + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("机构分润", context);
		String[] templateSheetName = {"sheet1"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出机构分润报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}

	public File tradeDetail(String startSettleDate, String endSettleDate) {
		List<TradeOrder> orderList = TradeOrder.dao.searchTradeDetailReport(startSettleDate, endSettleDate);
		if (orderList.isEmpty()) {
			return null;
		}
		for (TradeOrder order : orderList) {
			BigDecimal amount = BigDecimal.valueOf(order.getAmount()).divide(BigDecimal.valueOf(100));
			BigDecimal fee = BigDecimal.valueOf(order.getFee()).divide(BigDecimal.valueOf(100));
			BigDecimal balance = BigDecimal.valueOf(order.getBalance()).divide(BigDecimal.valueOf(100));
			if("df_personal".equals(order.getBizType())) {
				order.put("amountY", amount.divide(new BigDecimal(-1)));
				order.setBizType("对私代付");
			} else if("df_company".equals(order.getBizType())) {
				order.put("amountY", amount.divide(new BigDecimal(-1)));
				order.setBizType("对公代付");
			} else{
				order.put("amountY", amount);
				order.setBizType("充值");
			}
			order.put("feeY",fee.divide(new BigDecimal(-1)));
			order.put("balanceY", balance);
		}

		String fileName = "充值明细_" + startSettleDate + "至" + endSettleDate;
		Context context = new Context();
		context.putVar("fileName", fileName);
		context.putVar("list", orderList);
		String templatePath = FilePath.ReportTemplate.getValue() + "chargepay.xlsx";
		String targetPath = FilePath.ReportTmp.getValue() + fileName + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("充值明细", context);
		String[] templateSheetName = { "sheet1" };
		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出渠道交易明细报表发生异常");
			e.printStackTrace();
		}
		return new File(targetPath);
	}

	public File epaylinksPayOrders(String startSettleDate, String endSettleDate) {
		List<PayOrderItem> payOrderItemList = PayOrderItem.dao.find("SELECT * FROM pay_order_item WHERE orderStatus=? AND createDateTime BETWEEN ? AND ?",
				PayOrderItemStatus.OnePass.getValue(), startSettleDate + " 00:00:00", endSettleDate + " 23:59:59");
		if (payOrderItemList.isEmpty()) {
			return null;
		}

		List<PayOrderItem> reportList = new ArrayList<>(payOrderItemList.size());
		for (PayOrderItem item : payOrderItemList) {
			String type = item.getIsCompany() ? BizType.DF_Company.getValue() : BizType.DF_Personal.getValue();
			TradeConfig config = TradeConfig.dao.select(item.getMerchCode(), type);
			if (config == null) {
				continue;
			}

			if (Objects.equals(config.getChannelCode(), ChannelCode.Epaylinks.getValue())) {
				if (item.getIsCompany() == false) {
					/*CardBin cardBin = CardBin.dao.searchCodeByCardNo(item.getAccountNumber());
					if (cardBin != null) {
						String bankCode = cardBin.getOrgCode().substring(1, 4);
						item.setBankName(cardBin.getBankName());
						item.put("bankCode", bankCode);
					}*/
				}
				reportList.add(item);
			}
		}

		Context context = new Context();
		context.putVar("list", reportList);
		String createTime = ZODate.getDateTimeNumber();
		String templatePath = TEMPLATE + "epaylinks.xlsx";
		String targetPath = REPORT_PATH + createTime + ".xlsx";
		LinkedHashMap<String, Context> sheetNameAndData = new LinkedHashMap<>();
		sheetNameAndData.put("ypl", context);
		String[] templateSheetName = {"sheet1"};

		try {
			ZOExcel.exportLargeData(templatePath, targetPath, sheetNameAndData, templateSheetName);
		} catch (Exception e) {
			log.info("导出付款报表发生异常" + e);
		}
		File file = new File(targetPath);
		return file;
	}

}
